import datetime
import pandas as pd
import pymysql
import asyncio
import jieba

# 打开数据库连接
db_config = {
    'host': 'dc.unifound.net',  # 主机
    'user': 'root',  # 用户名
    'password': 'zjdx20210126',  # 密码
    'port': 63307,  # 端口 3306
    'database': 'uni_center'  # 数据库名
}
key_points_df = pd.DataFrame(columns=['datetime', 'value', 'event'])
# CREATE TABLE `book_link_teacher` (
#   `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
#   `bookId` int DEFAULT NULL COMMENT '推荐图书/教材ID',
#   `book` varchar(255) DEFAULT NULL COMMENT '图书名称',
#   `class_name` varchar(255) DEFAULT NULL COMMENT '课程名称',
#   `teacher` varchar(255) DEFAULT NULL COMMENT '关联教师',
#   `teacherId` int DEFAULT NULL COMMENT '关联教师ID',
#   PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# CREATE TABLE `book_major_recommend` (
#   `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
#   `bookId` int DEFAULT NULL COMMENT '推荐图书/教材ID',
#   `book` varchar(255) DEFAULT NULL COMMENT '推荐图书/教材',
#   `weight` double DEFAULT NULL COMMENT '权重',
#   `major` varchar(255) DEFAULT NULL COMMENT '关联专业',
#   `college` varchar(255) DEFAULT NULL COMMENT '关联学院',
#   `class` varchar(255) DEFAULT NULL COMMENT '关联班级',
#   `version` varchar(255) DEFAULT NULL COMMENT '版本',
#   PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# JGH 工号
# KCMC 课程名称
async def main():
    jieba.load_userdict("C:\\Users\\Administrator\\Desktop\\联创接口\\zjdx.txt")
    # seg_list = jieba.cut("我来到北京清华大学", cut_all=True)
    # print("Full Mode: " + "/ ".join(seg_list))  # 全模式

    # 记录函数开始时间
    start_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(f"开始执行时间: {start_time}")
    df = pd.read_excel("C:\\Users\\Administrator\\Desktop\\联创接口\\分析要求\\任务教材信息查询23241.xlsx")
    zhuanyes = df['专业']
    textbooks = df['教材名称']
    colleges = df['学院']
    college_class_s = df['班级']
    courses = df['课程名称']
    course_code_s = df['课程代码']
    # 数据库链接打开
    db = pymysql.connect(**db_config)
    # 使用cursor()方法获取操作游标a
    cursor = db.cursor()

    for i, d in zhuanyes.items():
        zhuanye = zhuanyes[i]
        textbook = textbooks[i]
        college = colleges[i]
        college_class = college_class_s[i]
        course = courses[i]
        course_code = course_code_s[i]
        map_booid_count = {}
        map_booid_name = {}
        # 切词
        seg_list = jieba.cut(textbook, cut_all=False)
        for seg in seg_list:
            if len(seg) >= 2:
                print(f"name -> {zhuanye} word -> {seg}")
                sql = "SELECT douban_id,book_name FROM org_base_1FE.word_book_tags where version = 6 and word = '{}'".format(
                    seg)
                cursor.execute(sql)
                # 获取所有记录列表
                results = cursor.fetchall()
                for row in results:
                    book_id = row[0]
                    book_name = row[1]
                    if book_id in map_booid_count:
                        count = map_booid_count[book_id]
                        count = count + 1
                        map_booid_count[book_id] = count
                    else:
                        map_booid_count[book_id] = 1
                        map_booid_name[book_id] = book_name

        for bid in map_booid_count:
            sql_insert = "INSERT INTO org_base_1FE.book_major_recommend(`bookId`, `book`, `weight`, `class_name`, `major`, `college`, `class`, `version`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
            data = (bid, map_booid_name[bid], map_booid_count[bid], course, zhuanye, college, college_class, 1)
            cursor.execute(sql_insert, data)
            db.commit()

        print(zhuanye)

    end_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    print(f"开始执行时间: {start_time} ，结束时间： {end_time}")
    # 新增 链接 teacher表
    # insert into book_link_teacher(`class_name`,`teacher`,`teacherId`) select KCMC,XM,KCH_ID from uni_center_test.jiaoshi where KCMC in (select class_name from book_major_recommend GROUP BY class_name)

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())